# _*_ coding:utf-8 _*_
# @Time  : 2022.06.20
# @Author: zizlee

# 品种仓单数据接口
import datetime

import pandas as pd
from fastapi import APIRouter, Query

from db import FAConnection
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse

variety_receipt_api = APIRouter()


@variety_receipt_api.get('/')  # 查询品种仓单数据
def get_variety_receipt(start: str = Query(...), end: str = Query(...), variety: str = Query(None)):
    start_ts = datetime_utils.auth_datetime_string(start, f='%Y%m%d', ts=True)
    end_ts = datetime_utils.auth_datetime_string(end, f='%Y%m%d', ts=True)
    if not all([start_ts, end_ts]):
        return AllResponse.validate_error(msg='日期格式错误!')
    variety_en = variety if variety else 'V'
    sql = """
        SELECT id,receipt_ts,variety_en,receipt,increase,ex_total FROM dat_futures_daily_receipt 
        WHERE receipt_ts>=%s AND receipt_ts<=%s AND ex_total=0 
            AND IF('V'=%s,TRUE,variety_en=%s);
    """
    prm = (start_ts, end_ts, variety_en, variety_en)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    if len(records) < 1:
        return AllResponse.server_no_content(msg='查询条件没有仓单数据。')
    df = pd.DataFrame(records)
    receipt_df = df.groupby(by=['receipt_ts', 'variety_en'], as_index=False)['receipt', 'increase'].sum()
    receipt_df['receipt_date'] = receipt_df['receipt_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    del receipt_df['receipt_ts']
    receipt_list = receipt_df.to_dict(orient='records')
    if variety_en != 'V':
        receipt_list = list(filter(lambda x: x['variety_en'] == variety_en, receipt_list))
    response_data = {
        'receipt_data': receipt_list
    }
    return AllResponse.operate_successfully(data=response_data)


@variety_receipt_api.get('/totalReceipt/')  # 品种的仓单数据
def variety_receipt(start_date: str = Query(...), v: str = Query(...)):
    start_ts = datetime_utils.auth_datetime_string(start_date, f='%Y-%m-%d', ts=True)
    if not start_ts:
        return AllResponse.validate_error(msg='日期格式错误')
    # 仅支持查询15天
    end_ts = int((datetime.datetime.fromtimestamp(int(start_ts)) + datetime.timedelta(days=15)).timestamp())
    with FAConnection() as cursor:
        cursor.execute("""
            SELECT FROM_UNIXTIME(receipt_ts,'%%Y-%%m-%%d') as receipt_date,variety_en,
            SUM(receipt) as t_receipt,
            SUM(increase) As t_increase
            FROM dat_futures_daily_receipt
            WHERE receipt_ts>=%s AND receipt_ts<=%s AND variety_en=%s AND ex_total=0
            GROUP BY variety_en,receipt_ts;
        """, [start_ts, end_ts, v])
        ret = cursor.fetchall()
    date_list = [r['receipt_date'] for r in ret]
    start_date = min(date_list)
    end_date = min(date_list)
    return AllResponse.operate_successfully(data={'data_list': ret,
                                                  'start_date': start_date,
                                                  'end_date': end_date})


@variety_receipt_api.get('/startDate/')  # 查询品种仓单数据开始的日期
def receipt_start(v: str = Query(...), ts: int = Query(None)):
    with FAConnection() as cursor:
        if ts:
            sql = """
                SELECT FROM_UNIXTIME(MIN(receipt_ts),'%%Y-%%m-%%d') As start_date FROM dat_futures_daily_receipt
                WHERE variety_en=%s AND receipt_ts>%s ORDER BY receipt_ts;
            """
            prm = [v, ts]
        else:
            sql = """
                SELECT FROM_UNIXTIME(MIN(receipt_ts),'%%Y-%%m-%%d') As start_date FROM dat_futures_daily_receipt
                WHERE variety_en=%s ORDER BY receipt_ts;
            """
            prm = [v]
        cursor.execute(sql, prm)
        ret = cursor.fetchone()['start_date']
    return AllResponse.operate_successfully(data={'start_date': ret})